Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Clusters

A cluster, sometimes called an index cluster, is an optional method of storing tables in an Oracle database. Within a cluster, multiple related tables are stored together to improve access time to the related items. Clusters are really useful only when the related data is often accessed together. The existence of a cluster is transparent to users and to applications; the cluster affects only how data is stored.

The use of a cluster can be advantageous in certain situations and disadvantageous in others. Be careful when determining whether a cluster can help performance in your configuration. Typically, clusters are advantageous if the related data that is clustered is primarily used in joins.

If you have two tables with related data that are frequently accessed together, using clusters can improve performance by preloading the related data into the SGA. Because you frequently use the data together, having that data already in the SGA greatly reduces access time.

Clusters are beneficial when joins occur on the cluster data because all the data is retrieved in one I/O operation. Following is an example of where a cluster would be beneficial.

Suppose that you are keeping a database of information for a dog club. (Because I am a dog lover and have several dogs of my own, I can easily relate to this topic.) In this database, you want to keep track of all the dogs and their owners as well as some information about each of the dogs. To do this, you must create several tables.

First, you need a table of all the dogs who are members of the dog club. You also need a table of the dog breeds and some information about the dog breeds (see Figure 10.1).


Figure 10.1  The DOGS table and the BREEDS table.

By combining the two tables into a cluster, you can save time when retrieving the data (because the breed information for a particular dog is essentially read into the SGA when the information for that dog is). The common columns of the cluster are called the cluster key, and must be indexed.

Figure 10.2 shows what the table looks like as a cluster. Note that the cluster key is the breed identification number.


Figure 10.2  The DOGS and BREEDS tables as a cluster.

If this information is frequently used together, this cluster arrangement is a performance win. The cluster has the ease of use of individual tables but the additional performance of the cluster.

If you do not typically use information together, there is no performance benefit of using a cluster. There is even a slight disadvantage because additional SGA space is taken up by the additional table information.

An additional disadvantage of clusters is a reduction of the performance of INSERT statements. This performance loss is caused by the additional complexity of the use of space and the fact that there are multiple tables in the same block. The clustered table also spans more blocks than the individual tables, thus causing more data to be scanned.

In summary, a cluster can be useful for tables where data is primarily accessed together in a join. The reduced I/O needed to bring the additional data into the SGA and the fact that the data is already cached can be a big advantage.

If the tables have a large number of INSERT statements or if the data is not frequently accessed together, a cluster is not useful and should not be used.

Do not cluster tables if full-table scans are often performed on only one of the tables in the cluster. The additional space required by the cluster and the additional I/O reduce performance.

Direct-Write Sorts

Using direct writes causes the server processes to write the output of sort operations directly to disk, bypassing the buffer cache. The effect of direct writes is that, for sort operations, large amounts of block buffers are not ejected from the buffer cache. This leaves the buffer cache available for normal queries and updates. By setting the Oracle initialization parameter SORT_DIRECT_WRITES to TRUE, you enable this feature.

Direct-write sorts take more memory than normal sorts. The amount of memory it uses can be determined with the following formula:

Direct Write Sort Memory = ( SORT_WRITE_BUFFERS ) * ( SORT_WRITE_BUFFER_SIZE )

Using direct-write sorts can improve performance both in the sort and in the entire system by not consuming the buffer cache. Only use direct-write sorts if you have sufficient memory and temporary disk space. The temporary disk space should have a sufficient I/O bandwidth to handle the load.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.